using AutoMapper;
using B.S.Data.MES.API.Applications.ShenchanInfoApplication.Commands;
using B.S.Domain.BasicData.MaterialManagement.Product_material;
using B.S.Domain.CangKu;
using B.S.Domain.Dto.ShenchanDto;
using B.S.Domain.Process.ProcessrouteName;
using B.S.Infratrctrue;
using B.S.Requst;
using MediatR;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace B.S.Data.MES.API.Applications.ShenchanInfoApplication.CommandHandlers
{
    /// <summary>
    /// 生产信息命令处理器
    /// </summary>
    public class ShenchanInfoCommandHandler :
        IRequestHandler<CreateShenchanInfoCommand, APIRequst<string>>,
        IRequestHandler<UpdateShenchanInfoCommand, APIRequst<int>>,
        IRequestHandler<DeleteShenchanInfoCommand, APIRequst<int>>,
        IRequestHandler<BatchDeleteShenchanInfoCommand, APIRequst<int>>,
        IRequestHandler<RestoreShenchanInfoCommand, APIRequst<int>>,
        IRequestHandler<BatchRestoreShenchanInfoCommand, APIRequst<int>>,
        IRequestHandler<QueryShenchanInfoListCommand, APIRequst<APIPageing<List<ShenchanInfoDetailDTO>>>>,
        IRequestHandler<GetShenchanInfoByIdCommand, APIRequst<ShenchanInfoDetailDTO>>,
        IRequestHandler<GetShenchanInfoByProductIdCommand, APIRequst<List<ShenchanInfoDetailDTO>>>,
        IRequestHandler<GetShenchanInfoByProcessrouteIdCommand, APIRequst<List<ShenchanInfoDetailDTO>>>,
        IRequestHandler<GetShenchanStatisticsCommand, APIRequst<ShenchanStatisticsDTO>>,
        IRequestHandler<DebugQueryCommand, object>,
        IRequestHandler<SimpleQueryCommand, object>,
        IRequestHandler<DetailedDebugQueryCommand, APIRequst<object>>,
        IRequestHandler<GetProcessrouteDropdownCommand, APIRequst<List<ProcessrouteDropdownDTO>>>
    {
        private readonly IBaseRepository<ShenchanInfo> _shenchanRepository;
        private readonly IBaseRepository<Product_material> _productRepository;
        private readonly IBaseRepository<Processroute> _processrouteRepository;
        private readonly IMapper _mapper;
        private readonly ILogger<ShenchanInfoCommandHandler> _logger;

        public ShenchanInfoCommandHandler(
            IBaseRepository<ShenchanInfo> shenchanRepository,
            IBaseRepository<Product_material> productRepository,
            IBaseRepository<Processroute> processrouteRepository,
            IMapper mapper,
            ILogger<ShenchanInfoCommandHandler> logger)
        {
            _shenchanRepository = shenchanRepository;
            _productRepository = productRepository;
            _processrouteRepository = processrouteRepository;
            _mapper = mapper;
            _logger = logger;
        }

        /// <summary>
        /// 创建生产信息
        /// </summary>
        public async Task<APIRequst<string>> Handle(CreateShenchanInfoCommand request, CancellationToken cancellationToken)
        {
            try
            {
                // 验证产品物料是否存在
                var product = await _productRepository.GetById(request.ShenchanInfo.Productid);
                if (product == null)
                {
                    return new APIRequst<string>
                    {
                        Code = RequstCode.失败,
                        Msg = "指定的产品物料不存在",
                        Data = string.Empty
                    };
                }

                // 验证工序路线是否存在
                var processroute = await _processrouteRepository.GetById(request.ShenchanInfo.Id);
                if (processroute == null)
                {
                    return new APIRequst<string>
                    {
                        Code = RequstCode.失败,
                        Msg = "指定的工序路线不存在",
                        Data = string.Empty
                    };
                }

                // 生成唯一ID：时间戳 + 序列号
                var timestamp = DateTimeOffset.Now.ToUnixTimeSeconds();
                var sequence = await GetNextSequence();
                var sid = $"{timestamp}{sequence:D3}";

                var shenchanInfo = new ShenchanInfo
                {
                    SID = sid,
                    Productid = request.ShenchanInfo.Productid,
                    Id = request.ShenchanInfo.Id,
                    ProductName = request.ShenchanInfo.ProductName,
                    ProductStandard = request.ShenchanInfo.ProductStandard,
                    ProductCount = request.ShenchanInfo.ProductCount,
                    ProductColor = request.ShenchanInfo.ProductColor
                };

                await _shenchanRepository.Add(shenchanInfo);

                _logger.LogInformation("创建生产信息成功，SID: {SID}", sid);

                return new APIRequst<string>
                {
                    Code = RequstCode.成功,
                    Msg = "生产信息创建成功",
                    Data = sid
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "创建生产信息失败: {Message}", ex.Message);
                return new APIRequst<string>
                {
                    Code = RequstCode.失败,
                    Msg = $"创建生产信息失败: {ex.Message}",
                    Data = string.Empty
                };
            }
        }

        /// <summary>
        /// 更新生产信息
        /// </summary>
        public async Task<APIRequst<int>> Handle(UpdateShenchanInfoCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var shenchanInfo = await _shenchanRepository.GetAll()
                    .FirstOrDefaultAsync(x => x.SID == request.ShenchanInfo.SID, cancellationToken);

                if (shenchanInfo == null)
                {
                    return new APIRequst<int>
                    {
                        Code = RequstCode.失败,
                        Msg = "生产信息不存在",
                        Data = 0
                    };
                }

                // 验证产品物料是否存在
                var product = await _productRepository.GetById(request.ShenchanInfo.Productid);
                if (product == null)
                {
                    return new APIRequst<int>
                    {
                        Code = RequstCode.失败,
                        Msg = "指定的产品物料不存在",
                        Data = 0
                    };
                }

                // 验证工序路线是否存在
                var processroute = await _processrouteRepository.GetById(request.ShenchanInfo.Id);
                if (processroute == null)
                {
                    return new APIRequst<int>
                    {
                        Code = RequstCode.失败,
                        Msg = "指定的工序路线不存在",
                        Data = 0
                    };
                }

                // 更新信息
                shenchanInfo.Productid = request.ShenchanInfo.Productid;
                shenchanInfo.Id = request.ShenchanInfo.Id;
                shenchanInfo.ProductName = request.ShenchanInfo.ProductName;
                shenchanInfo.ProductStandard = request.ShenchanInfo.ProductStandard;
                shenchanInfo.ProductCount = request.ShenchanInfo.ProductCount;
                shenchanInfo.ProductColor = request.ShenchanInfo.ProductColor;

                await _shenchanRepository.Update(shenchanInfo);

                _logger.LogInformation("更新生产信息成功，SID: {SID}", request.ShenchanInfo.SID);

                return new APIRequst<int>
                {
                    Code = RequstCode.成功,
                    Msg = "生产信息更新成功",
                    Data = 1
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新生产信息失败: {Message}", ex.Message);
                return new APIRequst<int>
                {
                    Code = RequstCode.失败,
                    Msg = $"更新生产信息失败: {ex.Message}",
                    Data = 0
                };
            }
        }

        /// <summary>
        /// 删除生产信息（逻辑删除）
        /// </summary>
        public async Task<APIRequst<int>> Handle(DeleteShenchanInfoCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var shenchanInfo = await _shenchanRepository.GetAll()
                    .Where(x => x.isDel == false || x.isDel == null) // 只查询未删除的记录
                    .FirstOrDefaultAsync(x => x.SID == request.SID, cancellationToken);

                if (shenchanInfo == null)
                {
                    return new APIRequst<int>
                    {
                        Code = RequstCode.失败,
                        Msg = "生产信息不存在或已被删除",
                        Data = 0
                    };
                }

                // 逻辑删除：设置isDel标志和删除信息
                shenchanInfo.isDel = true;
                shenchanInfo.DeleteTime = DateTime.Now;
                shenchanInfo.DeleteName = "系统用户"; // 可以根据实际需求传入当前用户名

                await _shenchanRepository.Update(shenchanInfo);

                _logger.LogInformation("逻辑删除生产信息成功，SID: {SID}", request.SID);

                return new APIRequst<int>
                {
                    Code = RequstCode.成功,
                    Msg = "生产信息删除成功",
                    Data = 1
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "删除生产信息失败: {Message}", ex.Message);
                return new APIRequst<int>
                {
                    Code = RequstCode.失败,
                    Msg = $"删除生产信息失败: {ex.Message}",
                    Data = 0
                };
            }
        }

        /// <summary>
        /// 批量删除生产信息（逻辑删除）
        /// </summary>
        public async Task<APIRequst<int>> Handle(BatchDeleteShenchanInfoCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var shenchanInfos = await _shenchanRepository.GetAll()
                    .Where(x => x.isDel == false || x.isDel == null) // 只查询未删除的记录
                    .Where(x => request.SIDs.Contains(x.SID))
                    .ToListAsync(cancellationToken);

                if (!shenchanInfos.Any())
                {
                    return new APIRequst<int>
                    {
                        Code = RequstCode.失败,
                        Msg = "没有找到要删除的生产信息或已被删除",
                        Data = 0
                    };
                }

                // 批量逻辑删除：设置isDel标志和删除信息
                foreach (var shenchanInfo in shenchanInfos)
                {
                    shenchanInfo.isDel = true;
                    shenchanInfo.DeleteTime = DateTime.Now;
                    shenchanInfo.DeleteName = "系统用户"; // 可以根据实际需求传入当前用户名
                }

                await _shenchanRepository.UpdateRange(shenchanInfos);

                _logger.LogInformation("批量逻辑删除生产信息成功，删除数量: {Count}", shenchanInfos.Count);

                return new APIRequst<int>
                {
                    Code = RequstCode.成功,
                    Msg = $"批量删除生产信息成功，删除数量: {shenchanInfos.Count}",
                    Data = shenchanInfos.Count
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量删除生产信息失败: {Message}", ex.Message);
                return new APIRequst<int>
                {
                    Code = RequstCode.失败,
                    Msg = $"批量删除生产信息失败: {ex.Message}",
                    Data = 0
                };
            }
        }

        /// <summary>
        /// 查询生产信息列表
        /// </summary>
        public async Task<APIRequst<APIPageing<List<ShenchanInfoDetailDTO>>>> Handle(QueryShenchanInfoListCommand request, CancellationToken cancellationToken)
        {
            try
            {
                _logger.LogInformation("开始查询生产信息列表，PageIndex: {PageIndex}, PageSize: {PageSize}", request.Query.PageIndex, request.Query.PageSize);
                
                // 先检查生产信息表数据
                var shenchanCount = await _shenchanRepository.GetAll().Where(x => x.isDel == false || x.isDel == null).CountAsync(cancellationToken);
                _logger.LogInformation("生产信息表未删除记录数: {Count}", shenchanCount);
                
                // 先只查询生产信息表，然后再尝试关联
                // 临时移除isDel条件，查询所有数据
                var shenchanQuery = _shenchanRepository.GetAll().Where(o=>o.isDel == false);
                
                // 检查是否有数据
                var shenchanList = await shenchanQuery.ToListAsync(cancellationToken);
                _logger.LogInformation("找到生产信息记录: {Count}", shenchanList.Count);
                
                if (shenchanList.Count == 0)
                {
                    _logger.LogWarning("生产信息表中没有未删除的记录");
                    return new APIRequst<APIPageing<List<ShenchanInfoDetailDTO>>>
                    {
                        Code = RequstCode.成功,
                        Msg = "查询生产信息列表成功",
                        Data = new APIPageing<List<ShenchanInfoDetailDTO>>
                        {
                            Total = 0,
                            TotalCount = 0,
                            Data = new List<ShenchanInfoDetailDTO>()
                        }
                    };
                }
                
                // 简化：直接使用生产信息表，不做复杂查询
                var baseQuery = shenchanQuery;
                
                _logger.LogInformation("基础查询构建完成");

                // 应用查询条件 - 暂时注释掉所有条件，只保留isDel筛选
                /*
                if (!string.IsNullOrEmpty(request.Query.SID))
                {
                    _logger.LogInformation("应用SID筛选条件: {SID}", request.Query.SID);
                    baseQuery = baseQuery.Where(x => x.SID == request.Query.SID);
                }

                if (request.Query.Productid.HasValue && request.Query.Productid.Value > 0)
                {
                    _logger.LogInformation("应用产品ID筛选条件: {Productid}", request.Query.Productid.Value);
                    baseQuery = baseQuery.Where(x => x.Productid == request.Query.Productid.Value);
                }

                if (request.Query.ProcessrouteId.HasValue && request.Query.ProcessrouteId.Value > 0)
                {
                    _logger.LogInformation("应用工序路线ID筛选条件: {ProcessrouteId}", request.Query.ProcessrouteId.Value);
                    baseQuery = baseQuery.Where(x => x.Id == request.Query.ProcessrouteId.Value);
                }

                if (!string.IsNullOrEmpty(request.Query.ProductName))
                {
                    _logger.LogInformation("应用产品名称筛选条件: {ProductName}", request.Query.ProductName);
                    baseQuery = baseQuery.Where(x => x.ProductName.Contains(request.Query.ProductName));
                }

                if (!string.IsNullOrEmpty(request.Query.ProductColor))
                {
                    _logger.LogInformation("应用产品颜色筛选条件: {ProductColor}", request.Query.ProductColor);
                    baseQuery = baseQuery.Where(x => x.ProductColor.Contains(request.Query.ProductColor));
                }

                if (request.Query.MinCount.HasValue && request.Query.MinCount.Value >= 0)
                {
                    _logger.LogInformation("应用最小数量筛选条件: {MinCount}", request.Query.MinCount.Value);
                    baseQuery = baseQuery.Where(x => x.ProductCount >= request.Query.MinCount.Value);
                }

                if (request.Query.MaxCount.HasValue && request.Query.MaxCount.Value > 0)
                {
                    _logger.LogInformation("应用最大数量筛选条件: {MaxCount}", request.Query.MaxCount.Value);
                    baseQuery = baseQuery.Where(x => x.ProductCount <= request.Query.MaxCount.Value);
                }
                */

                // 计算总数
                var total = await baseQuery.CountAsync(cancellationToken);
                _logger.LogInformation("查询到的总记录数: {Total}", total);

                // 防止PageSize为0导致的除零错误
                var pageSize = request.Query.PageSize > 0 ? request.Query.PageSize : 10;
                var pageIndex = request.Query.PageIndex > 0 ? request.Query.PageIndex : 1;

                // 分页查询 - 直接获取生产信息数据
                var shenchanItems = await baseQuery
                    .OrderByDescending(x => x.SID)
                    .Skip((pageIndex - 1) * pageSize)
                    .Take(pageSize)
                    .ToListAsync(cancellationToken);
                
                _logger.LogInformation("分页查询完成，返回记录数: {Count}", shenchanItems.Count);

                // 获取关联数据的ID列表
                var productIds = shenchanItems.Select(s => s.Productid).Distinct().ToList();
                var processrouteIds = shenchanItems.Select(s => s.Id).Distinct().ToList();

                // 批量查询关联数据
                var products = await _productRepository.GetAll()
                    .Where(x => (x.isDel == false || x.isDel == null) && productIds.Contains(x.Productid))
                    .ToListAsync(cancellationToken);

                var processroutes = await _processrouteRepository.GetAll()
                    .Where(x => (x.IsDel == false || x.IsDel == null) && processrouteIds.Contains(x.Id))
                    .ToListAsync(cancellationToken);

                _logger.LogInformation("关联数据查询完成 - 产品: {ProductCount}, 工序路线: {ProcessrouteCount}", 
                    products.Count, processroutes.Count);

                // 转换为DTO
                var result = shenchanItems.Select(shenchan => 
                {
                    var product = products.FirstOrDefault(p => p.Productid == shenchan.Productid);
                    var processroute = processroutes.FirstOrDefault(pr => pr.Id == shenchan.Id);

                    return new ShenchanInfoDetailDTO
                    {
                        SID = shenchan.SID,
                        Productid = shenchan.Productid,
                        Id = shenchan.Id,
                        ProductName = shenchan.ProductName,
                        ProductStandard = shenchan.ProductStandard,
                        ProductCount = shenchan.ProductCount,
                        ProductColor = shenchan.ProductColor,
                        ProductMaterial = product != null ? new ProductMaterialInfo
                        {
                            Productid = product.Productid,
                            Productcode = product.Productcode ?? "",
                            ProductName = product.ProductName ?? "",
                            Specifications = product.specifications ?? "",
                            Unit = product.unit ?? "",
                            Earlywarningvalue = product.Earlywarningvalue,
                            Describe = product.describe ?? ""
                        } : new ProductMaterialInfo
                        {
                            Productid = shenchan.Productid,
                            Productcode = "未找到",
                            ProductName = "未找到产品信息",
                            Specifications = "",
                            Unit = "",
                            Earlywarningvalue = 0,
                            Describe = ""
                        },
                        Processroute = processroute != null ? new ProcessrouteInfo
                        {
                            Id = processroute.Id,
                            ProcessrouteName = processroute.ProcessrouteName ?? "",
                            Image = processroute.Image ?? "",
                            ProcessrouteType = processroute.ProcessrouteType ?? "",
                            ProcessrouteState = processroute.ProcessrouteState,
                            ProcessrouteDescription = processroute.ProcessrouteDescription ?? "",
                            ProcessrouteCreatorName = processroute.ProcessrouteCreatorName ?? "",
                            ProcessrouteCreatorTime = processroute.ProcessrouteCreatorTime
                        } : new ProcessrouteInfo
                        {
                            Id = shenchan.Id,
                            ProcessrouteName = "未找到工序路线",
                            Image = "",
                            ProcessrouteType = "",
                            ProcessrouteState = false,
                            ProcessrouteDescription = "",
                            ProcessrouteCreatorName = "",
                            ProcessrouteCreatorTime = DateTime.MinValue
                        }
                    };
                }).ToList();

                var totalPages = total > 0 ? (int)Math.Ceiling((double)total / pageSize) : 0;
                
                var pageResult = new APIPageing<List<ShenchanInfoDetailDTO>>
                {
                    Total = totalPages,
                    TotalCount = total,
                    Data = result
                };
                
                _logger.LogInformation("查询生产信息列表完成，总页数: {TotalPages}, 总记录数: {TotalCount}, 当前页记录数: {CurrentCount}", 
                    totalPages, total, result.Count);

                return new APIRequst<APIPageing<List<ShenchanInfoDetailDTO>>>
                {
                    Code = RequstCode.成功,
                    Msg = "查询生产信息列表成功",
                    Data = pageResult
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询生产信息列表失败: {Message}", ex.Message);
                return new APIRequst<APIPageing<List<ShenchanInfoDetailDTO>>>
                {
                    Code = RequstCode.失败,
                    Msg = $"查询生产信息列表失败: {ex.Message}",
                    Data = new APIPageing<List<ShenchanInfoDetailDTO>>
                    {
                        Total = 0,
                        TotalCount = 0,
                        Data = new List<ShenchanInfoDetailDTO>()
                    }
                };
            }
        }

        /// <summary>
        /// 根据ID查询生产信息详情
        /// </summary>
        public async Task<APIRequst<ShenchanInfoDetailDTO>> Handle(GetShenchanInfoByIdCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var query = from s in _shenchanRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) // 只查询未删除的记录
                           join p in _productRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) on s.Productid equals p.Productid
                           join pr in _processrouteRepository.GetAll().Where(x => (x.IsDel == false || x.IsDel == null)) on s.Id equals pr.Id
                           where s.SID == request.SID
                           select new { ShenchanInfo = s, Product = p, Processroute = pr };

                var item = await query.FirstOrDefaultAsync(cancellationToken);

                if (item == null)
                {
                    return new APIRequst<ShenchanInfoDetailDTO>
                    {
                        Code = RequstCode.失败,
                        Msg = "生产信息不存在",
                        Data = null!
                    };
                }

                var result = new ShenchanInfoDetailDTO
                {
                    SID = item.ShenchanInfo.SID,
                    Productid = item.ShenchanInfo.Productid,
                    Id = item.ShenchanInfo.Id,
                    ProductName = item.ShenchanInfo.ProductName,
                    ProductStandard = item.ShenchanInfo.ProductStandard,
                    ProductCount = item.ShenchanInfo.ProductCount,
                    ProductColor = item.ShenchanInfo.ProductColor,
                    ProductMaterial = new ProductMaterialInfo
                    {
                        Productid = item.Product.Productid,
                        Productcode = item.Product.Productcode,
                        ProductName = item.Product.ProductName,
                        Specifications = item.Product.specifications,
                        Unit = item.Product.unit,
                        Earlywarningvalue = item.Product.Earlywarningvalue,
                        Describe = item.Product.describe
                    },
                    Processroute = new ProcessrouteInfo
                    {
                        Id = item.Processroute.Id,
                        ProcessrouteName = item.Processroute.ProcessrouteName,
                        Image = item.Processroute.Image,
                        ProcessrouteType = item.Processroute.ProcessrouteType,
                        ProcessrouteState = item.Processroute.ProcessrouteState,
                        ProcessrouteDescription = item.Processroute.ProcessrouteDescription,
                        ProcessrouteCreatorName = item.Processroute.ProcessrouteCreatorName,
                        ProcessrouteCreatorTime = item.Processroute.ProcessrouteCreatorTime
                    }
                };

                return new APIRequst<ShenchanInfoDetailDTO>
                {
                    Code = RequstCode.成功,
                    Msg = "查询生产信息详情成功",
                    Data = result
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询生产信息详情失败: {Message}", ex.Message);
                return new APIRequst<ShenchanInfoDetailDTO>
                {
                    Code = RequstCode.失败,
                    Msg = $"查询生产信息详情失败: {ex.Message}",
                    Data = null!
                };
            }
        }

        /// <summary>
        /// 根据产品物料ID查询生产信息
        /// </summary>
        public async Task<APIRequst<List<ShenchanInfoDetailDTO>>> Handle(GetShenchanInfoByProductIdCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var query = from s in _shenchanRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) // 只查询未删除的记录
                           join p in _productRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) on s.Productid equals p.Productid
                           join pr in _processrouteRepository.GetAll().Where(x => (x.IsDel == false || x.IsDel == null)) on s.Id equals pr.Id
                           where s.Productid == request.ProductId
                           select new { ShenchanInfo = s, Product = p, Processroute = pr };

                var items = await query
                    .OrderByDescending(x => x.ShenchanInfo.SID)
                    .ToListAsync(cancellationToken);

                var result = items.Select(x => new ShenchanInfoDetailDTO
                {
                    SID = x.ShenchanInfo.SID,
                    Productid = x.ShenchanInfo.Productid,
                    Id = x.ShenchanInfo.Id,
                    ProductName = x.ShenchanInfo.ProductName,
                    ProductStandard = x.ShenchanInfo.ProductStandard,
                    ProductCount = x.ShenchanInfo.ProductCount,
                    ProductColor = x.ShenchanInfo.ProductColor,
                    ProductMaterial = new ProductMaterialInfo
                    {
                        Productid = x.Product.Productid,
                        Productcode = x.Product.Productcode,
                        ProductName = x.Product.ProductName,
                        Specifications = x.Product.specifications,
                        Unit = x.Product.unit,
                        Earlywarningvalue = x.Product.Earlywarningvalue,
                        Describe = x.Product.describe
                    },
                    Processroute = new ProcessrouteInfo
                    {
                        Id = x.Processroute.Id,
                        ProcessrouteName = x.Processroute.ProcessrouteName,
                        Image = x.Processroute.Image,
                        ProcessrouteType = x.Processroute.ProcessrouteType,
                        ProcessrouteState = x.Processroute.ProcessrouteState,
                        ProcessrouteDescription = x.Processroute.ProcessrouteDescription,
                        ProcessrouteCreatorName = x.Processroute.ProcessrouteCreatorName,
                        ProcessrouteCreatorTime = x.Processroute.ProcessrouteCreatorTime
                    }
                }).ToList();

                return new APIRequst<List<ShenchanInfoDetailDTO>>
                {
                    Code = RequstCode.成功,
                    Msg = "根据产品物料ID查询生产信息成功",
                    Data = result
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据产品物料ID查询生产信息失败: {Message}", ex.Message);
                return new APIRequst<List<ShenchanInfoDetailDTO>>
                {
                    Code = RequstCode.失败,
                    Msg = $"根据产品物料ID查询生产信息失败: {ex.Message}",
                    Data = new List<ShenchanInfoDetailDTO>()
                };
            }
        }

        /// <summary>
        /// 根据工序路线ID查询生产信息
        /// </summary>
        public async Task<APIRequst<List<ShenchanInfoDetailDTO>>> Handle(GetShenchanInfoByProcessrouteIdCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var query = from s in _shenchanRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) // 只查询未删除的记录
                           join p in _productRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) on s.Productid equals p.Productid
                           join pr in _processrouteRepository.GetAll().Where(x => (x.IsDel == false || x.IsDel == null)) on s.Id equals pr.Id
                           where s.Id == request.ProcessrouteId
                           select new { ShenchanInfo = s, Product = p, Processroute = pr };

                var items = await query
                    .OrderByDescending(x => x.ShenchanInfo.SID)
                    .ToListAsync(cancellationToken);

                var result = items.Select(x => new ShenchanInfoDetailDTO
                {
                    SID = x.ShenchanInfo.SID,
                    Productid = x.ShenchanInfo.Productid,
                    Id = x.ShenchanInfo.Id,
                    ProductName = x.ShenchanInfo.ProductName,
                    ProductStandard = x.ShenchanInfo.ProductStandard,
                    ProductCount = x.ShenchanInfo.ProductCount,
                    ProductColor = x.ShenchanInfo.ProductColor,
                    ProductMaterial = new ProductMaterialInfo
                    {
                        Productid = x.Product.Productid,
                        Productcode = x.Product.Productcode,
                        ProductName = x.Product.ProductName,
                        Specifications = x.Product.specifications,
                        Unit = x.Product.unit,
                        Earlywarningvalue = x.Product.Earlywarningvalue,
                        Describe = x.Product.describe
                    },
                    Processroute = new ProcessrouteInfo
                    {
                        Id = x.Processroute.Id,
                        ProcessrouteName = x.Processroute.ProcessrouteName,
                        Image = x.Processroute.Image,
                        ProcessrouteType = x.Processroute.ProcessrouteType,
                        ProcessrouteState = x.Processroute.ProcessrouteState,
                        ProcessrouteDescription = x.Processroute.ProcessrouteDescription,
                        ProcessrouteCreatorName = x.Processroute.ProcessrouteCreatorName,
                        ProcessrouteCreatorTime = x.Processroute.ProcessrouteCreatorTime
                    }
                }).ToList();

                return new APIRequst<List<ShenchanInfoDetailDTO>>
                {
                    Code = RequstCode.成功,
                    Msg = "根据工序路线ID查询生产信息成功",
                    Data = result
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据工序路线ID查询生产信息失败: {Message}", ex.Message);
                return new APIRequst<List<ShenchanInfoDetailDTO>>
                {
                    Code = RequstCode.失败,
                    Msg = $"根据工序路线ID查询生产信息失败: {ex.Message}",
                    Data = new List<ShenchanInfoDetailDTO>()
                };
            }
        }

        /// <summary>
        /// 获取生产统计信息
        /// </summary>
        public async Task<APIRequst<ShenchanStatisticsDTO>> Handle(GetShenchanStatisticsCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var query = from s in _shenchanRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) // 只查询未删除的记录
                           join p in _productRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) on s.Productid equals p.Productid
                           join pr in _processrouteRepository.GetAll().Where(x => (x.IsDel == false || x.IsDel == null)) on s.Id equals pr.Id
                           select new { ShenchanInfo = s, Product = p, Processroute = pr };

                // 应用时间过滤
                if (request.StartDate.HasValue)
                {
                    var startTimeStamp = new DateTimeOffset(request.StartDate.Value).ToUnixTimeSeconds().ToString();
                    query = query.Where(x => string.Compare(x.ShenchanInfo.SID, startTimeStamp) >= 0);
                }

                if (request.EndDate.HasValue)
                {
                    var endTimeStamp = new DateTimeOffset(request.EndDate.Value.AddDays(1)).ToUnixTimeSeconds().ToString();
                    query = query.Where(x => string.Compare(x.ShenchanInfo.SID, endTimeStamp) < 0);
                }

                // 应用产品过滤
                if (request.ProductId.HasValue)
                    query = query.Where(x => x.ShenchanInfo.Productid == request.ProductId);

                var data = await query.ToListAsync(cancellationToken);

                var statistics = new ShenchanStatisticsDTO
                {
                    TotalRecords = data.Count,
                    TotalProductCount = data.Sum(x => x.ShenchanInfo.ProductCount),
                    ProductVarieties = data.Select(x => x.ShenchanInfo.Productid).Distinct().Count(),
                    ProcessrouteCount = data.Select(x => x.ShenchanInfo.Id).Distinct().Count(),
                    ProductStatistics = data
                        .GroupBy(x => new { x.Product.Productid, x.Product.ProductName, x.Product.Productcode })
                        .Select(g => new ProductStatisticsItem
                        {
                            ProductId = g.Key.Productid,
                            ProductName = g.Key.ProductName,
                            ProductCode = g.Key.Productcode,
                            RecordCount = g.Count(),
                            TotalCount = g.Sum(x => x.ShenchanInfo.ProductCount)
                        }).ToList(),
                    ProcessrouteStatistics = data
                        .GroupBy(x => new { x.Processroute.Id, x.Processroute.ProcessrouteName, x.Processroute.ProcessrouteType })
                        .Select(g => new ProcessrouteStatisticsItem
                        {
                            ProcessrouteId = g.Key.Id,
                            ProcessrouteName = g.Key.ProcessrouteName,
                            ProcessrouteType = g.Key.ProcessrouteType,
                            RecordCount = g.Count(),
                            TotalCount = g.Sum(x => x.ShenchanInfo.ProductCount)
                        }).ToList()
                };

                return new APIRequst<ShenchanStatisticsDTO>
                {
                    Code = RequstCode.成功,
                    Msg = "获取生产统计信息成功",
                    Data = statistics
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取生产统计信息失败: {Message}", ex.Message);
                return new APIRequst<ShenchanStatisticsDTO>
                {
                    Code = RequstCode.失败,
                    Msg = $"获取生产统计信息失败: {ex.Message}",
                    Data = new ShenchanStatisticsDTO()
                };
            }
        }

        /// <summary>
        /// 恢复删除的生产信息
        /// </summary>
        public async Task<APIRequst<int>> Handle(RestoreShenchanInfoCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var shenchanInfo = await _shenchanRepository.GetAll()
                    .Where(x => x.isDel) // 只查询已删除的记录
                    .FirstOrDefaultAsync(x => x.SID == request.SID, cancellationToken);

                if (shenchanInfo == null)
                {
                    return new APIRequst<int>
                    {
                        Code = RequstCode.失败,
                        Msg = "生产信息不存在或未被删除",
                        Data = 0
                    };
                }

                // 恢复删除：重置isDel标志
                shenchanInfo.isDel = false;
                shenchanInfo.UpdateTime = DateTime.Now;
                shenchanInfo.UpdateName = "系统用户"; // 可以根据实际需求传入当前用户名
                // 清空删除信息
                shenchanInfo.DeleteTime = null;
                shenchanInfo.DeleteName = null;

                await _shenchanRepository.Update(shenchanInfo);

                _logger.LogInformation("恢复生产信息成功，SID: {SID}", request.SID);

                return new APIRequst<int>
                {
                    Code = RequstCode.成功,
                    Msg = "生产信息恢复成功",
                    Data = 1
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "恢复生产信息失败: {Message}", ex.Message);
                return new APIRequst<int>
                {
                    Code = RequstCode.失败,
                    Msg = $"恢复生产信息失败: {ex.Message}",
                    Data = 0
                };
            }
        }

        /// <summary>
        /// 批量恢复删除的生产信息
        /// </summary>
        public async Task<APIRequst<int>> Handle(BatchRestoreShenchanInfoCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var shenchanInfos = await _shenchanRepository.GetAll()
                    .Where(x => x.isDel) // 只查询已删除的记录
                    .Where(x => request.SIDs.Contains(x.SID))
                    .ToListAsync(cancellationToken);

                if (!shenchanInfos.Any())
                {
                    return new APIRequst<int>
                    {
                        Code = RequstCode.失败,
                        Msg = "没有找到要恢复的生产信息或未被删除",
                        Data = 0
                    };
                }

                // 批量恢复删除：重置isDel标志
                foreach (var shenchanInfo in shenchanInfos)
                {
                    shenchanInfo.isDel = false;
                    shenchanInfo.UpdateTime = DateTime.Now;
                    shenchanInfo.UpdateName = "系统用户"; // 可以根据实际需求传入当前用户名
                    // 清空删除信息
                    shenchanInfo.DeleteTime = null;
                    shenchanInfo.DeleteName = null;
                }

                await _shenchanRepository.UpdateRange(shenchanInfos);

                _logger.LogInformation("批量恢复生产信息成功，恢复数量: {Count}", shenchanInfos.Count);

                return new APIRequst<int>
                {
                    Code = RequstCode.成功,
                    Msg = $"批量恢复生产信息成功，恢复数量: {shenchanInfos.Count}",
                    Data = shenchanInfos.Count
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量恢复生产信息失败: {Message}", ex.Message);
                return new APIRequst<int>
                {
                    Code = RequstCode.失败,
                    Msg = $"批量恢复生产信息失败: {ex.Message}",
                    Data = 0
                };
            }
        }

        /// <summary>
        /// 调试查询 - 检查数据库中的数据情况
        /// </summary>
        public async Task<object> Handle(DebugQueryCommand request, CancellationToken cancellationToken)
        {
            try
            {
                // 检查生产信息表
                var allShenchanInfo = await _shenchanRepository.GetAll().ToListAsync(cancellationToken);
                var activeShenchanInfo = await _shenchanRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)).ToListAsync(cancellationToken);

                // 检查产品物料表
                var allProducts = await _productRepository.GetAll().ToListAsync(cancellationToken);
                var activeProducts = await _productRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)).ToListAsync(cancellationToken);

                // 检查工序路线表
                var allProcessroutes = await _processrouteRepository.GetAll().ToListAsync(cancellationToken);
                var activeProcessroutes = await _processrouteRepository.GetAll().Where(x => (x.IsDel == false || x.IsDel == null)).ToListAsync(cancellationToken);

                // 检查关联查询
                var joinQuery = from s in _shenchanRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null))
                               join p in _productRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) on s.Productid equals p.Productid into productJoin
                               from p in productJoin.DefaultIfEmpty()
                               join pr in _processrouteRepository.GetAll().Where(x => (x.IsDel == false || x.IsDel == null)) on s.Id equals pr.Id into processrouteJoin
                               from pr in processrouteJoin.DefaultIfEmpty()
                               select new { ShenchanInfo = s, Product = p, Processroute = pr };

                var joinResults = await joinQuery.ToListAsync(cancellationToken);

                return new
                {
                    ShenchanInfoTable = new
                    {
                        TotalCount = allShenchanInfo.Count,
                        ActiveCount = activeShenchanInfo.Count,
                        DeletedCount = allShenchanInfo.Count - activeShenchanInfo.Count,
                        SampleData = activeShenchanInfo.Take(3).Select(x => new
                        {
                            x.SID,
                            x.Productid,
                            x.Id,
                            x.ProductName,
                            x.isDel
                        })
                    },
                    ProductTable = new
                    {
                        TotalCount = allProducts.Count,
                        ActiveCount = activeProducts.Count,
                        DeletedCount = allProducts.Count - activeProducts.Count,
                        SampleData = activeProducts.Take(3).Select(x => new
                        {
                            x.Productid,
                            x.ProductName,
                            x.isDel
                        })
                    },
                    ProcessrouteTable = new
                    {
                        TotalCount = allProcessroutes.Count,
                        ActiveCount = activeProcessroutes.Count,
                        DeletedCount = allProcessroutes.Count - activeProcessroutes.Count,
                        SampleData = activeProcessroutes.Take(3).Select(x => new
                        {
                            x.Id,
                            x.ProcessrouteName,
                            x.IsDel
                        })
                    },
                    JoinResults = new
                    {
                        Count = joinResults.Count,
                        SampleData = joinResults.Take(3).Select(x => new
                        {
                            ShenchanSID = x.ShenchanInfo?.SID,
                            ProductName = x.Product?.ProductName,
                            ProcessrouteName = x.Processroute?.ProcessrouteName,
                            HasProduct = x.Product != null,
                            HasProcessroute = x.Processroute != null
                        })
                    },
                    PossibleIssues = new List<string>()
                    {
                        allShenchanInfo.Count == 0 ? "生产信息表为空" : null,
                        allProducts.Count == 0 ? "产品物料表为空" : null,
                        allProcessroutes.Count == 0 ? "工序路线表为空" : null,
                        activeShenchanInfo.Count == 0 && allShenchanInfo.Count > 0 ? "所有生产信息都被逻辑删除了" : null,
                        joinResults.Count == 0 && activeShenchanInfo.Count > 0 ? "关联查询失败，可能是外键不匹配" : null
                    }.Where(x => x != null).ToList()
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "调试查询失败: {Message}", ex.Message);
                return new { Error = ex.Message, StackTrace = ex.StackTrace };
            }
        }

        /// <summary>
        /// 简化查询 - 只查询生产信息表
        /// </summary>
        public async Task<object> Handle(SimpleQueryCommand request, CancellationToken cancellationToken)
        {
            try
            {
                _logger.LogInformation("开始简化查询生产信息");

                // 只查询生产信息表，不做关联
                var allData = await _shenchanRepository.GetAll().ToListAsync(cancellationToken);
                var activeData = await _shenchanRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)).ToListAsync(cancellationToken);

                var result = new
                {
                    TotalRecords = allData.Count,
                    ActiveRecords = activeData.Count,
                    DeletedRecords = allData.Count - activeData.Count,
                    SampleActiveData = activeData.Take(5).Select(x => new
                    {
                        x.SID,
                        x.Productid,
                        x.Id,
                        x.ProductName,
                        x.ProductStandard,
                        x.ProductCount,
                        x.ProductColor,
                        x.isDel,
                        x.CreateTime,
                        x.UpdateTime
                    }).ToList(),
                    SampleAllData = allData.Take(5).Select(x => new
                    {
                        x.SID,
                        x.Productid,
                        x.Id,
                        x.ProductName,
                        x.ProductStandard,
                        x.ProductCount,
                        x.ProductColor,
                        x.isDel,
                        x.CreateTime,
                        x.UpdateTime
                    }).ToList()
                };

                _logger.LogInformation("简化查询完成，总记录: {Total}, 活跃记录: {Active}, 删除记录: {Deleted}", 
                    result.TotalRecords, result.ActiveRecords, result.DeletedRecords);

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "简化查询失败: {Message}", ex.Message);
                return new { Error = ex.Message, StackTrace = ex.StackTrace };
            }
        }

        /// <summary>
        /// 获取下一个序列号
        /// </summary>
        private async Task<int> GetNextSequence()
        {
            var today = DateTime.Today;
            var tomorrow = today.AddDays(1);
            var todayTimestamp = new DateTimeOffset(today).ToUnixTimeSeconds().ToString();
            var tomorrowTimestamp = new DateTimeOffset(tomorrow).ToUnixTimeSeconds().ToString();

            var todayCount = await _shenchanRepository.GetAll()
                .Where(x => (x.isDel == false || x.isDel == null)) // 只计算未删除的记录
                .Where(x => string.Compare(x.SID, todayTimestamp) >= 0 && string.Compare(x.SID, tomorrowTimestamp) < 0)
                .CountAsync();

            return todayCount + 1;
        }

        /// <summary>
        /// 详细调试查询处理 - 逐步分析主查询问题
        /// </summary>
        public async Task<APIRequst<object>> Handle(DetailedDebugQueryCommand request, CancellationToken cancellationToken)
        {
            try
            {
                _logger.LogInformation("开始详细调试查询");

                // 第1步：检查生产信息表数据
                var allShenchan = await _shenchanRepository.GetAll().ToListAsync(cancellationToken);
                var activeShenchan = await _shenchanRepository.GetAll()
                    .Where(x => x.isDel == false || x.isDel == null)
                    .ToListAsync(cancellationToken);

                _logger.LogInformation("生产信息表 - 总数: {Total}, 活跃数: {Active}", allShenchan.Count, activeShenchan.Count);

                // 第2步：检查产品物料表数据
                var allProducts = await _productRepository.GetAll().ToListAsync(cancellationToken);
                var activeProducts = await _productRepository.GetAll()
                    .Where(x => x.isDel == false || x.isDel == null)
                    .ToListAsync(cancellationToken);

                _logger.LogInformation("产品物料表 - 总数: {Total}, 活跃数: {Active}", allProducts.Count, activeProducts.Count);

                // 第3步：检查工序路线表数据
                var allProcessroutes = await _processrouteRepository.GetAll().ToListAsync(cancellationToken);
                var activeProcessroutes = await _processrouteRepository.GetAll()
                    .Where(x => x.IsDel == false || x.IsDel == null)
                    .ToListAsync(cancellationToken);

                _logger.LogInformation("工序路线表 - 总数: {Total}, 活跃数: {Active}", allProcessroutes.Count, activeProcessroutes.Count);

                // 第4步：检查关联关系
                var productMatches = new List<object>();
                var processrouteMatches = new List<object>();

                foreach (var shenchan in activeShenchan)
                {
                    var matchingProduct = activeProducts.FirstOrDefault(p => p.Productid == shenchan.Productid);
                    var matchingProcessroute = activeProcessroutes.FirstOrDefault(pr => pr.Id == shenchan.Id);

                    productMatches.Add(new 
                    { 
                        ShenchanSID = shenchan.SID,
                        ShenchanProductid = shenchan.Productid,
                        HasMatchingProduct = matchingProduct != null,
                        ProductName = matchingProduct?.ProductName ?? "未找到"
                    });

                    processrouteMatches.Add(new 
                    { 
                        ShenchanSID = shenchan.SID,
                        ShenchanId = shenchan.Id,
                        HasMatchingProcessroute = matchingProcessroute != null,
                        ProcessrouteName = matchingProcessroute?.ProcessrouteName ?? "未找到"
                    });
                }

                // 第5步：执行实际的LEFT JOIN查询
                var shenchanQuery = _shenchanRepository.GetAll().Where(x => x.isDel == false || x.isDel == null);
                var joinQuery = from s in shenchanQuery
                               join p in _productRepository.GetAll().Where(x => (x.isDel == false || x.isDel == null)) on s.Productid equals p.Productid into productJoin
                               from p in productJoin.DefaultIfEmpty()
                               join pr in _processrouteRepository.GetAll().Where(x => (x.IsDel == false || x.IsDel == null)) on s.Id equals pr.Id into processrouteJoin
                               from pr in processrouteJoin.DefaultIfEmpty()
                               select new { ShenchanInfo = s, Product = p, Processroute = pr };

                var joinResults = await joinQuery.ToListAsync(cancellationToken);

                _logger.LogInformation("LEFT JOIN查询结果数量: {Count}", joinResults.Count);

                var result = new
                {
                    Step1_ShenchanInfo = new { Total = allShenchan.Count, Active = activeShenchan.Count },
                    Step2_ProductMaterial = new { Total = allProducts.Count, Active = activeProducts.Count },
                    Step3_Processroute = new { Total = allProcessroutes.Count, Active = activeProcessroutes.Count },
                    Step4_ProductMatches = productMatches,
                    Step5_ProcessrouteMatches = processrouteMatches,
                    Step6_JoinResults = joinResults.Count,
                    Step7_SampleJoinData = joinResults.Take(3).Select(x => new 
                    {
                        SID = x.ShenchanInfo.SID,
                        ProductName = x.ShenchanInfo.ProductName,
                        HasProduct = x.Product != null,
                        HasProcessroute = x.Processroute != null,
                        ProductInfo = x.Product != null ? $"ID:{x.Product.Productid}, Name:{x.Product.ProductName}" : "null",
                        ProcessrouteInfo = x.Processroute != null ? $"ID:{x.Processroute.Id}, Name:{x.Processroute.ProcessrouteName}" : "null"
                    }).ToList()
                };

                return new APIRequst<object>
                {
                    Code = RequstCode.成功,
                    Msg = "详细调试查询完成",
                    Data = result
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "详细调试查询失败: {Message}", ex.Message);
                return new APIRequst<object>
                {
                    Code = RequstCode.失败,
                    Msg = $"详细调试查询失败: {ex.Message}",
                    Data = new { Error = ex.Message, StackTrace = ex.StackTrace }
                };
            }
        }

        /// <summary>
        /// 获取工序路线下拉框数据处理
        /// </summary>
        public async Task<APIRequst<List<ProcessrouteDropdownDTO>>> Handle(GetProcessrouteDropdownCommand request, CancellationToken cancellationToken)
        {
            try
            {
                _logger.LogInformation("开始获取工序路线下拉框数据");

                // 先检查所有数据
                var allProcessroutes = await _processrouteRepository.GetAll().ToListAsync(cancellationToken);
                _logger.LogInformation("数据库中总工序路线数: {Total}", allProcessroutes.Count);
                
                // 记录每条数据的IsDel状态
                foreach (var pr in allProcessroutes.Take(5)) // 只记录前5条
                {
                    _logger.LogInformation("工序路线 ID:{Id}, Name:{Name}, IsDel:{IsDel}", pr.Id, pr.ProcessrouteName, pr.IsDel);
                }

                // 查询所有未删除的工序路线 - 只要IsDel == false
                var processroutes = await _processrouteRepository.GetAll()
                    .Where(x => x.IsDel == false)
                    .OrderBy(x => x.ProcessrouteName)
                    .Select(x => new ProcessrouteDropdownDTO
                    {
                        Id = x.Id,
                        ProcessrouteName = x.ProcessrouteName ?? ""
                    })
                    .ToListAsync(cancellationToken);

                _logger.LogInformation("获取工序路线下拉框数据完成，共 {Count} 条记录", processroutes.Count);

                return new APIRequst<List<ProcessrouteDropdownDTO>>
                {
                    Code = RequstCode.成功,
                    Msg = "获取工序路线下拉框数据成功",
                    Data = processroutes
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取工序路线下拉框数据失败: {Message}", ex.Message);
                return new APIRequst<List<ProcessrouteDropdownDTO>>
                {
                    Code = RequstCode.失败,
                    Msg = $"获取工序路线下拉框数据失败: {ex.Message}",
                    Data = new List<ProcessrouteDropdownDTO>()
                };
            }
        }

        //qwqwqwqwqwqwqwqqw
    }
}
